import gl
import sqlite3
from pkg.voice import out


def build_user_msg_tab():
    conn = sqlite3.connect(gl.g_db_name)
    cur = conn.cursor()
    cur.execute('SELECT id FROM USER')
    result = cur.fetchall()
    for row in result:
        cur.execute('CREATE TABLE IF NOT EXISTS '+row[0]+'(attribute_name VARCHAR(64) NOT NULL,attribute_value BLOB NOT NULL)')


def get_users():
    try:
        conn = sqlite3.connect(gl.g_db_name)
        cur = conn.cursor()
        cur.execute('SELECT * FROM USER')
        result=cur.fetchall()
        if len(result) == 0:
            return None
        return result
    except Exception as Err:
        print(Err)
        out.output('数据库发生错误')
        return None


def is_user_exist(user):
    try:
        conn = sqlite3.connect(gl.g_db_name)
        cur = conn.cursor()
        cur.execute('SELECT id FROM USER WHERE id="'+user+'"')
        t=cur.fetchall()
        return len(t)!=0
    except Exception as Err:
        print(Err)
        out.output('数据库发生错误')
        return False


def add_user(data):
    try:
        conn = sqlite3.connect(gl.g_db_name)
        cur = conn.cursor()
        cur.execute('INSERT INTO USER VALUES (?,?,?,?)',data)
        conn.commit()
        return True
    except Exception as Err:
        print(Err)
        out.output('数据库发生错误')
        return False


def get_user_info(user_id):
    try:
        conn = sqlite3.connect(gl.g_db_name)
        cur = conn.cursor()
        cur.execute('SELECT * FROM USER WHERE id="'+user_id+'"')
        t = cur.fetchall()
        if len(t)!=1:
            out.output('数据库异常')
            return None
        return t[0]
    except Exception as Err:
        print(Err)
        out.output('数据库发生错误')
        return None


def exec(sql):
    try:
        conn = sqlite3.connect(gl.g_db_name)
        cur = conn.cursor()
        cur.execute(sql)
        conn.commit()
        return True
    except Exception as Err:
        print(Err)
        out.output('数据库发生错误')
        return False


def select_exec(sql):
    try:
        conn = sqlite3.connect(gl.g_db_name)
        cur = conn.cursor()
        cur.execute(sql)
        conn.commit()
        t = cur.fetchall()
        content=''
        for i in t:
            content+=str(i)+'\n'
        return content,True
    except Exception as Err:
        print(Err)
        out.output('数据库发生错误')
        return '',False


def get_conn():
    try:
        conn = sqlite3.connect(gl.g_db_name)
        cur = conn.cursor()
        return conn,cur
    except Exception as Err:
        print(Err)
        out.output('数据库发生错误')
        return None,None